with wide_unsign as (
    select t1.waybill_no
         , t1.deliver_later_scantime      --最后一次派件时间
         , t1.outstore_early_scantime     --第一次出库时间
         , t1.sign_early_scantime         --第一次签收时间
         , t1.deliver_early_scansitecode  --第一次派件网点
         , t1.outstore_early_scansitecode --第一次出库网点
         , t1.sign_early_scansitecode     --第一次签收网点
         , case
               when t1.sign_early_scantime is not null and t1.outstore_early_scantime is not null
                   then least(t1.sign_early_scantime, t1.outstore_early_scantime)
               else coalesce(t1.sign_early_scantime, t1.outstore_early_scantime) end as sign_out_time

         , case
               when t1.sign_early_scantime is null then t1.outstore_early_scansitecode
               when t1.outstore_early_scantime is null then sign_early_scansitecode
               when sign_early_scantime < t1.outstore_early_scantime then sign_early_scansitecode
               else outstore_early_scansitecode end                                  as sign_out_code
         , coalesce(t1.send_sign_usercode_early, t1.send_deliver_usercode_latest)    as sign_deliver_user_code
    from jms_dwd.dwd_wide_unsign_summary_waybill_dt t1
             join
         (
             select waybill_id
             from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt
             where dt <= '{{ execution_date | cst_ds }}'
               and dt >= date_add('{{ execution_date | cst_ds }}', -14)
               and field_name = 'Special_need'
             group by waybill_id
         ) t2
         on t1.waybill_no = t2.waybill_id
    where t1.dt <= '{{ execution_date | cst_ds }}'
      and t1.dt >= date_add('{{ execution_date | cst_ds }}', -44)
      and (
            substr(t1.deliver_early_scantime, 1, 10) between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
            or substr(t1.outstore_early_scantime, 1, 10) between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
            or substr(t1.sign_early_scantime, 1, 10) between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
        )
),
     door_sign as (
         select waybill_no
              , is_door       --是否送货上门，1是，0否
              , source        --设备来源
              , business_name --末端品牌名称
              , store_name    --门店名称
              , is_reback     --是否退转件，1：是，0：否
         from (
                  select waybill_no
                       , is_door       --是否送货上门，1是，0否
                       , source        --设备来源
                       , business_name --末端品牌名称
                       , store_name    --门店名称
                       , is_reback     --是否退转件，1：是，0：否
                       , row_number() over (partition by waybill_no order by dt desc ) as rn
                  from jms_dm.dm_door_sign_rate_detail_dt
                  where dt <= '{{ execution_date | cst_ds }}'
                    and dt >= date_add('{{ execution_date | cst_ds }}', -6)
              ) a
         where rn = 1
     )
insert
overwrite
table
jms_dm.dm_mng_special_need_waybill_detail_mid_dt
partition
(
dt
)
select /* +BROADCAST(tab_shipment) */
    t1.waybill_no                                                as waybill_no            --运单号
     , t1.outstore_early_scantime                                as outstore_scantime     --宽表出库时间
     , t1.sign_early_scantime                                    as sign_scantime         --宽表签收时间
     , t1.deliver_early_scansitecode                             as deliver_scansitecode  --宽表派件网点
     , t1.outstore_early_scansitecode                            as outstore_scansitecode --宽表出库网点
     , t1.sign_early_scansitecode                                as sign_scansitecode     --宽表签收网点
     , coalesce(t1.sign_out_code, t1.deliver_early_scansitecode) as network_code          --网点编码
     , t1.sign_deliver_user_code                                 as deliver_user_code     --最晚派件员编码
     , t8.name                                                   as deliver_user_name     --最晚派件员名字
     , substr(t2.receiver_detailed_address, 1, 120)              as receiver_address      --收件地址
     , t1.deliver_later_scantime                                 as deliver_scantime      --派件时间
     , t1.sign_out_time                                          as sign_time             --签收时间
     , t3.is_door                                                as is_door               --是否送货上门1是0否
     , t3.source                                                 as source                --设备来源
     , t3.business_name                                          as business_name         --末端品牌名称
     , t3.store_name                                             as store_name            --门店名称
     , t3.is_reback                                              as is_reback             --是否退转件1是0否
     , t4.is_contact                                             as is_contact            --是否电联0否1是
     , t5.connect_status                                         as connect_status        --0未接通1已接通本方挂断2已接通对方挂断
     , t5.duration_seconds                                       as duration_seconds      --通话时长（秒）
     , if(t6.waybill_no is not null, 1, 0)                       as is_send_msg           --是否发送短信1是0否
     , t6.send_time                                              as send_time             --发送时间
     , t6.template_name                                          as template_name         --模板名称
     , t7.name                                                   as network_name          --网点名称
     , t7.agent_code                                             as agent_code            --代理区编码
     , t7.agent_name                                             as agent_name            --代理区名称
     , t7.fran_code                                              as fran_code             --加盟商区编码
     , t7.fran_name                                              as fran_name             --加盟商区名称
     , t7.virt_code                                              as virt_code             --虚拟代理区编码
     , t7.virt_name                                              as virt_name             --虚拟代理区名称
     , '{{ execution_date | cst_ds }}'                                              as date_time             --日期t-1
     , '{{ execution_date | cst_ds }}'                                              as dt                    --分区
from wide_unsign t1
         left join
(--订单表
    select waybill_id
         , substr(receiver_detailed_address, 1, 120) as receiver_detailed_address
    from jms_dwd.dwd_yl_oms_oms_order_dt
    where dt <= '{{ execution_date | cst_ds }}'
      and dt >= date_add('{{ execution_date | cst_ds }}', -15)
) t2
on t1.waybill_no = t2.waybill_id
         left join door_sign t3
                   on t1.waybill_no = t3.waybill_no
         left join
(--订单履约表
    select *
    from (
             select waybill_id
                  , is_contact --是否电联 0:否1:是
                  , row_number() over (partition by waybill_id order by update_time desc ) as rn
             from jms_dwd.dwd_oms_order_performance_main_base_dt
             where dt <= '{{ execution_date | cst_ds }}'
               and dt >= date_add('{{ execution_date | cst_ds }}', -14)
         ) a
    where rn = 1
) t4
on t1.waybill_no = t4.waybill_id
         left join
(--通话记录表
    select *
    from (
             select waybill_no       -- 运单编号
                  , duration_seconds -- 通话时长（秒）
                  , connect_status   -- 0未接通 1已接通本方挂断 2已接通对方挂断
                  , row_number() over (partition by waybill_no order by update_time desc ) as rn
             from jms_dwd.dwd_sqs_call_record_base_dt
             where dt between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'
         ) a
    where rn = 1
) t5
on t1.waybill_no = t5.waybill_no
         left join
(--短信记录小时表
    select *
    from (
             select waybill_no
                  , send_time                                                            as send_time     --发送时间
                  , template_name                                                        as template_name --模板名称
                  , row_number() over (partition by waybill_no order by send_time desc ) as rn
             from jms_dwd.dwd_yl_sms_send_detail_base_hi
             where dt <= concat('{{ execution_date | cst_ds }}', '-23')
               and dt >= concat(date_add('{{ execution_date | cst_ds }}', -7), '-00')
         ) a
    where rn = 1
) t6
on t1.waybill_no = t6.waybill_no
         left join jms_dim.dim_network_whole_massage t7
                   on coalesce(t1.sign_out_code, t1.deliver_early_scansitecode) = t7.code
         left join
(--员工
    select code, name
    from jms_dim.dim_lmdm_sys_staff
    group by code, name
) t8
on t1.sign_deliver_user_code = t8.code
    distribute by dt
   , pmod(hash(waybill_no), 2)
;
